﻿-- =============================================
-- Author:		Dmitry Nazarov
-- Create date: 23.03.2011
-- Description:	Отчет выставленные авансы
-- =============================================
CREATE PROCEDURE [dbo].[report_exposed_advance] 
	-- Add the parameters for the stored procedure here
	@p_date_from date, 
	@p_date_to date,
	@p_nsi_type_of_activity_id int,
	@p_nsi_organization_id int,
	@p_contract_id	int,
	@p_sum_from money,
	@p_sum_to money
AS
BEGIN
	SET NOCOUNT ON;

    SELECT     o.SHORT_NAME AS ORGANIZATION_NAME, io.NUMBER AS INVOICE_NUMBER, io.DATE AS INVOICE_DATE, io.ADVANCE_PERCENT, io.DESCRIPTION, 
                      c.NUMBER AS CONTRACT_NUMBER, pu.NAME AS PRODUCT_NAME, cp.NUMBER AS CUST_PROTOCOL_NUMBER, COUNT(*) AS PRODUCT_COUNT,
                      [io].[SUM] as INVOICE_SUM, SUM(ioi.[SUM] ) as INV_ITEM_SUM,
					  dbo.f_get_currency_name_by_id([io].NSI_CURRENCY_ID ) as CUR_NAME,
                      dbo.f_convert_to_rur(dbo.f_GetInvOutRest(io.ID) , GETDATE(), io.NSI_CURRENCY_ID) AS REST_SUM,
                      (dbo.f_convert_to_rur(dbo.f_GetInvOutRest(io.ID) , GETDATE(), io.NSI_CURRENCY_ID))/(dbo.CountInvRepeatsInRepExpAdv(io.ID)) as PROPORTIONAL_REST_SUM                     
FROM         INVOICE_OUT AS io INNER JOIN
                      INVOICE_OUT_ITEM AS ioi ON io.ID = ioi.INVOICE_OUT_ID INNER JOIN
                      VIEW_SEARCH_P_U_FOR_FORMS AS pu ON pu.ID = ioi.PRODUCT_UNIT_ID INNER JOIN
                      PRODUCT_COST AS pc ON pu.ID = pc.PRODUCT_UNIT_ID INNER JOIN
                      COST_PROTOCOL AS cp ON cp.ID = pc.COST_PROTOCOL_ID LEFT OUTER JOIN
                      NSI_ORGANIZATION AS o ON io.NSI_ORGANIZATION_ID = o.ID INNER JOIN
                      PID ON PID.ID = io.PID INNER JOIN
                      [CONTRACT] AS c ON c.ID = PID.CONTR_ID --LEFT OUTER JOIN
                      --PAY_IN_ON_INV_OUT as pioi ON [io].ID = pioi.INVOICE_OUT_ID
WHERE     ((pu.NSI_PRODUCT_STATE_ID <= 3) AND (ISNULL(pu.INVOICE_IN_SUM, 0) - ISNULL(pu.PAYMENT_IN_SUM_CUR, 0) > 0) AND (io.DATE BETWEEN 
                      @p_date_from AND @p_date_to) AND (@p_nsi_type_of_activity_id IS NULL) AND (@p_nsi_organization_id IS NULL) AND (@p_contract_id IS NULL) OR
                      (pu.NSI_PRODUCT_STATE_ID <= 3) AND (ISNULL(pu.INVOICE_IN_SUM, 0) - ISNULL(pu.PAYMENT_IN_SUM_CUR, 0) > 0) AND (io.DATE BETWEEN 
                      @p_date_from AND @p_date_to) AND (@p_nsi_organization_id IS NULL) AND (@p_contract_id IS NULL) AND 
                      (c.NSI_TYPE_OF_ACTIVITY_ID = @p_nsi_type_of_activity_id) OR
                      (pu.NSI_PRODUCT_STATE_ID <= 3) AND (ISNULL(pu.INVOICE_IN_SUM, 0) - ISNULL(pu.PAYMENT_IN_SUM_CUR, 0) > 0) AND (io.DATE BETWEEN 
                      @p_date_from AND @p_date_to) AND (@p_nsi_type_of_activity_id IS NULL) AND (@p_contract_id IS NULL) AND 
                      (c.NSI_ORGANIZATION_ID = @p_nsi_organization_id) OR
                      (pu.NSI_PRODUCT_STATE_ID <= 3) AND (ISNULL(pu.INVOICE_IN_SUM, 0) - ISNULL(pu.PAYMENT_IN_SUM_CUR, 0) > 0) AND (io.DATE BETWEEN 
                      @p_date_from AND @p_date_to) AND (@p_contract_id IS NULL) AND (c.NSI_TYPE_OF_ACTIVITY_ID = @p_nsi_type_of_activity_id) AND 
                      (c.NSI_ORGANIZATION_ID = @p_nsi_organization_id) OR
                      (pu.NSI_PRODUCT_STATE_ID <= 3) AND (ISNULL(pu.INVOICE_IN_SUM, 0) - ISNULL(pu.PAYMENT_IN_SUM_CUR, 0) > 0) AND (io.DATE BETWEEN 
                      @p_date_from AND @p_date_to) AND (@p_nsi_type_of_activity_id IS NULL) AND (@p_nsi_organization_id IS NULL) AND (c.ID = @p_contract_id) OR
                      (pu.NSI_PRODUCT_STATE_ID <= 3) AND (ISNULL(pu.INVOICE_IN_SUM, 0) - ISNULL(pu.PAYMENT_IN_SUM_CUR, 0) > 0) AND (io.DATE BETWEEN 
                      @p_date_from AND @p_date_to) AND (@p_nsi_organization_id IS NULL) AND (c.NSI_TYPE_OF_ACTIVITY_ID = @p_nsi_type_of_activity_id) AND 
                      (c.ID = @p_contract_id) OR
                      (pu.NSI_PRODUCT_STATE_ID <= 3) AND (ISNULL(pu.INVOICE_IN_SUM, 0) - ISNULL(pu.PAYMENT_IN_SUM_CUR, 0) > 0) AND (io.DATE BETWEEN 
                      @p_date_from AND @p_date_to) AND (@p_nsi_type_of_activity_id IS NULL) AND (c.NSI_ORGANIZATION_ID = @p_nsi_organization_id) AND 
                      (c.ID = @p_contract_id) OR
                      (pu.NSI_PRODUCT_STATE_ID <= 3) AND (ISNULL(pu.INVOICE_IN_SUM, 0) - ISNULL(pu.PAYMENT_IN_SUM_CUR, 0) > 0) AND (io.DATE BETWEEN 
                      @p_date_from AND @p_date_to) AND (c.NSI_TYPE_OF_ACTIVITY_ID = @p_nsi_type_of_activity_id) AND 
                      (c.NSI_ORGANIZATION_ID = @p_nsi_organization_id) AND (c.ID = @p_contract_id))
                      --AND
                      --(
						--(@p_sum_from is NULL)
						--OR
						--(ioi.SUM BETWEEN @p_sum_from AND @p_sum_to)
                     -- )
GROUP BY o.SHORT_NAME, c.NUMBER, io.NUMBER,io.DESCRIPTION, io.SUM, io.DATE, io.ADVANCE_PERCENT,  pu.NAME, cp.NUMBER,  io.NSI_CURRENCY_ID, io.ID
END